import asyncio
from utils.db_manager import DBManager

async def create_tables():
    """创建数据库表并添加初始数据"""
    try:
        db_manager = DBManager()
        conn = db_manager.get_connection()
        cursor = conn.cursor()
        
        # 1. 创建模型表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS models (
                id INT AUTO_INCREMENT PRIMARY KEY,
                model_name VARCHAR(255) NOT NULL,
                model_type VARCHAR(50) NOT NULL,
                source_lang CHAR(2),
                target_lang CHAR(2),
                provider VARCHAR(50),
                model_path VARCHAR(255),
                config JSON,
                is_active BOOLEAN DEFAULT true,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY unique_model (model_name, model_type, source_lang, target_lang)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        """)
        
        # 2. 创建技术术语表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS technical_terms (
                id INT AUTO_INCREMENT PRIMARY KEY,
                source_term VARCHAR(255) NOT NULL,
                target_term VARCHAR(255) NOT NULL,
                source_lang CHAR(2) NOT NULL,
                target_lang CHAR(2) NOT NULL,
                category VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY term_pair (source_term, target_term, source_lang, target_lang)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        """)
        
        # 3. 创建模型使用记录表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS model_usage (
                id INT AUTO_INCREMENT PRIMARY KEY,
                model_id INT NOT NULL,
                request_type VARCHAR(50),
                input_length INT,
                output_length INT,
                processing_time FLOAT,
                status VARCHAR(20),
                error_message TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (model_id) REFERENCES models(id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        """)
        
        # 4. 插入翻译模型数据
        cursor.execute("""
            INSERT IGNORE INTO models 
            (model_name, model_type, source_lang, target_lang, provider, model_path, config)
            VALUES
            ('Helsinki-NLP/opus-mt-en-zh', 'translation', 'en', 'zh', 'huggingface', 
             'Helsinki-NLP/opus-mt-en-zh', '{"max_length": 512, "num_beams": 4}'),
            ('Helsinki-NLP/opus-mt-zh-en', 'translation', 'zh', 'en', 'huggingface',
             'Helsinki-NLP/opus-mt-zh-en', '{"max_length": 512, "num_beams": 4}'),
            ('facebook/nllb-200-distilled-600M', 'translation', null, null, 'huggingface',
             'facebook/nllb-200-distilled-600M', '{"max_length": 512, "num_beams": 5}')
        """)
        
        # 5. 插入技术术语数据
        cursor.execute("""
            INSERT IGNORE INTO technical_terms 
            (source_term, target_term, source_lang, target_lang, category)
            VALUES
            -- 机器学习领域 (英译中)
            ('neural network', '神经网络', 'en', 'zh', 'machine_learning'),
            ('neural networks', '神经网络', 'en', 'zh', 'machine_learning'),
            ('deep learning', '深度学习', 'en', 'zh', 'machine_learning'),
            ('machine learning', '机器学习', 'en', 'zh', 'machine_learning'),
            ('artificial intelligence', '人工智能', 'en', 'zh', 'machine_learning'),
            ('ai', '人工智能', 'en', 'zh', 'machine_learning'),
            ('backpropagation', '反向传播', 'en', 'zh', 'machine_learning'),
            ('back propagation', '反向传播', 'en', 'zh', 'machine_learning'),
            ('back-propagation', '反向传播', 'en', 'zh', 'machine_learning'),
            ('weights', '权重', 'en', 'zh', 'machine_learning'),
            ('weight', '权重', 'en', 'zh', 'machine_learning'),
            ('training', '训练', 'en', 'zh', 'machine_learning'),
            ('train', '训练', 'en', 'zh', 'machine_learning'),
            ('inference', '推理', 'en', 'zh', 'machine_learning'),
            ('infer', '推理', 'en', 'zh', 'machine_learning'),
            ('optimization', '优化', 'en', 'zh', 'machine_learning'),
            ('optimize', '优化', 'en', 'zh', 'machine_learning'),
            ('gradient descent', '梯度下降', 'en', 'zh', 'machine_learning'),
            
            -- 机器学习领域 (中译英)
            ('神经网络', 'neural network', 'zh', 'en', 'machine_learning'),
            ('深度学习', 'deep learning', 'zh', 'en', 'machine_learning'),
            ('机器学习', 'machine learning', 'zh', 'en', 'machine_learning'),
            ('人工智能', 'artificial intelligence', 'zh', 'en', 'machine_learning'),
            ('反向传播', 'backpropagation', 'zh', 'en', 'machine_learning'),
            ('权重', 'weight', 'zh', 'en', 'machine_learning'),
            ('训练', 'training', 'zh', 'en', 'machine_learning'),
            ('推理', 'inference', 'zh', 'en', 'machine_learning'),
            ('优化', 'optimization', 'zh', 'en', 'machine_learning'),
            ('梯度下降', 'gradient descent', 'zh', 'en', 'machine_learning'),
            
            -- 编程领域 (英译中)
            ('programming', '编程', 'en', 'zh', 'programming'),
            ('code', '代码', 'en', 'zh', 'programming'),
            ('function', '函数', 'en', 'zh', 'programming'),
            ('class', '类', 'en', 'zh', 'programming'),
            ('object', '对象', 'en', 'zh', 'programming'),
            ('variable', '变量', 'en', 'zh', 'programming'),
            ('loop', '循环', 'en', 'zh', 'programming'),
            ('condition', '条件', 'en', 'zh', 'programming'),
            ('database', '数据库', 'en', 'zh', 'programming'),
            ('algorithm', '算法', 'en', 'zh', 'programming'),
            
            -- 编程领域 (中译英)
            ('编程', 'programming', 'zh', 'en', 'programming'),
            ('代码', 'code', 'zh', 'en', 'programming'),
            ('函数', 'function', 'zh', 'en', 'programming'),
            ('类', 'class', 'zh', 'en', 'programming'),
            ('对象', 'object', 'zh', 'en', 'programming'),
            ('变量', 'variable', 'zh', 'en', 'programming'),
            ('循环', 'loop', 'zh', 'en', 'programming'),
            ('条件', 'condition', 'zh', 'en', 'programming'),
            ('数据库', 'database', 'zh', 'en', 'programming'),
            ('算法', 'algorithm', 'zh', 'en', 'programming')
        """)
        
        conn.commit()
        cursor.close()
        conn.close()
        
        print("Tables and initial data created successfully!")
        
    except Exception as e:
        print(f"Error creating tables and data: {str(e)}")

if __name__ == "__main__":
    asyncio.run(create_tables()) 